use anyhow::{Result, anyhow};
use serde::Serialize;
use sqlx::mysql::MySqlRow;
use sqlx::{Execute, QueryBuilder, Row};
use time::{PrimitiveDateTime, UtcDateTime, UtcOffset};

use crate::core::common::Sex;
use crate::core::db_base::get_conn;

#[derive(Debug, Serialize)]
pub(crate) struct Student {
    pub(crate) id: u32,
    pub(crate) create_time: PrimitiveDateTime,
    pub(crate) name: String,
    pub(crate) sex: Sex,
    pub(crate) age: u8,
    pub(crate) email: String,
    pub(crate) remark: Option<String>,
}
impl Student {
    pub fn new(name: String, sex: Sex, age: u8, email: String, remark: Option<String>) -> Self {
        let date_offset = UtcOffset::from_hms(8, 0, 0).expect("8 hours offset time is not valid");
        let utc_now = UtcDateTime::now();
        let local_now = utc_now.to_offset(date_offset);
        Self {
            id: 0,
            create_time: PrimitiveDateTime::new(local_now.date(), local_now.time()),
            name,
            sex,
            age,
            email,
            remark,
        }
    }
    fn map_row2stu(row: MySqlRow) -> Student {
        let sex: Sex = row.get::<u8, &str>("sex").try_into().unwrap();
        Student {
            id: row.get("id"),
            create_time: row.get("create_time"),
            name: row.get("name"),
            sex: sex,
            age: row.get("age"),
            email: row.get("email"),
            remark: row.get("remark"),
        }
    }
    pub async fn list_all_student(
        name: Option<impl AsRef<str>>,
        page_index: u8,
        page_size: u8,
    ) -> Result<Vec<Self>> {
        let conn = get_conn().await?;
        let mut builder =
            QueryBuilder::new("SELECT id,create_time,name,sex,age,email,remark FROM students");
        if let Some(name) = name {
            builder.push(" where name like");
            builder.push_bind(format!("%{}%", name.as_ref()));
        }
        builder
            .push(" limit ")
            .push_bind((page_index - 1) * page_size)
            .push(",")
            .push_bind(page_size)
            .push(";");

        let query = builder.build();
        println!("{}", query.sql());

        let students = query.map(Self::map_row2stu).fetch_all(&conn).await?;
        Ok(students)
    }

    pub async fn find_by_name(name: impl AsRef<str>) -> Result<Option<Self>> {
        let conn = get_conn().await?;
        let stu = sqlx::query(
            "SELECT id,create_time,name,sex,age,email,remark FROM students where name=?;",
        )
        .bind(name.as_ref())
        .map(Self::map_row2stu)
        .fetch_optional(&conn)
        .await?;
        Ok(stu)
    }

    pub async fn add_new(&self) -> Result<()> {
        let conn = get_conn().await?;
        //此处应该查重
        let execute_result= sqlx::query("insert into students ( create_time, name, sex, age, email, remark) values (NOW(),?,?,?,?,?);")
            .bind(&self.name)
            .bind(self.sex as u8)
            .bind(self.age)
            .bind(&self.email)
            .bind(&self.remark)
            .execute(&conn).await?;
        if execute_result.rows_affected() > 0 {
            Ok(())
        } else {
            Err(anyhow!("执行成功,但未真正影响数据"))
        }
    }
    pub async fn del(&self) -> Result<()> {
        if self.id.eq(&0) {
            return Err(anyhow!("id can't be zero"));
        }
        let conn = get_conn().await?;
        let result = sqlx::query("delete from students where id = ?")
            .bind(self.id)
            .execute(&conn)
            .await?;
        if result.rows_affected() > 0 {
            Ok(())
        } else {
            Err(anyhow!("执行成功,但未真正影响数据"))
        }
    }
}

#[cfg(test)]
mod test {
    use super::*;
    use anyhow::Result;

    #[actix_web::test]
    async fn list_all_student_test_ok() -> Result<()> {
        let stus = Student::list_all_student(Some(""), 1, 10).await;
        match stus {
            Ok(studs) => {
                assert!(studs.len() > 0);
            }
            Err(err) => {
                panic!("{err}");
            }
        }
        Ok(())
    }

    #[actix_web::test]
    async fn list_all_student_test2_ok() -> Result<()> {
        let stus = Student::list_all_student(None::<&str>, 1, 10).await;
        match stus {
            Ok(studs) => {
                assert!(studs.len() > 0);
            }
            Err(err) => {
                panic!("{err}");
            }
        }
        Ok(())
    }

    #[actix_web::test]
    async fn add_del_student_ok() -> Result<()> {
        let stu = Student::new(
            "test_std".into(),
            Sex::Male,
            11,
            "test_stu@hotmail.com".into(),
            None,
        );
        stu.add_new().await?;
        let student = Student::find_by_name("test_std").await?;
        let stu = match student {
            Some(stu) => {
                assert_eq!(stu.sex, Sex::Male);
                assert_eq!(stu.age, 11);
                assert_eq!(stu.email, "test_stu@hotmail.com");
                assert_eq!(stu.remark, None);
                stu
            }
            None => {
                panic!("can't find user from db, but insert db is return true;");
            }
        };
        stu.del().await?;

        let student = Student::find_by_name("test_std").await?;
        match student {
            Some(stu) => {
                panic!("user is deleted, but there is one in db;");
            }
            None => {}
        }

        Ok(())
    }
}
